require('dotenv').config();
const mysql = require('mysql2');

// 创建数据库连接（不指定数据库）
const connection = mysql.createConnection({
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || 'password'
});

// 创建数据库
connection.query('CREATE DATABASE IF NOT EXISTS xiqu_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci', (err, results) => {
  if (err) {
    console.error('创建数据库失败:', err);
    connection.end();
    return;
  }
  
  console.log('数据库创建成功');
  
  // 重新连接到指定数据库
  connection.changeUser({ database: 'xiqu_db' }, (err) => {
    if (err) {
      console.error('切换数据库失败:', err);
      connection.end();
      return;
    }
    
    // 读取并执行 schema.sql
    const fs = require('fs');
    const path = require('path');
    
    const schemaSql = fs.readFileSync(path.join(__dirname, '../config/schema.sql'), 'utf8');
    
    // 分割并执行多个语句
    const statements = schemaSql.split(';').filter(stmt => stmt.trim() !== '');
    
    let index = 0;
    function executeNext() {
      if (index >= statements.length) {
        console.log('数据库表创建完成');
        
        // 执行初始数据插入
        const dataSql = fs.readFileSync(path.join(__dirname, '../config/data.sql'), 'utf8');
        const dataStatements = dataSql.split(';').filter(stmt => stmt.trim() !== '');
        
        let dataIndex = 0;
        function executeNextData() {
          if (dataIndex >= dataStatements.length) {
            console.log('初始数据插入完成');
            connection.end();
            return;
          }
          
          const statement = dataStatements[dataIndex].trim() + ';';
          if (statement.trim() !== ';') {
            connection.query(statement, (err, results) => {
              if (err) {
                console.error('执行SQL失败:', err);
              } else {
                console.log(`数据语句 ${dataIndex + 1} 执行成功`);
              }
              dataIndex++;
              executeNextData();
            });
          } else {
            dataIndex++;
            executeNextData();
          }
        }
        
        executeNextData();
        return;
      }
      
      const statement = statements[index].trim() + ';';
      if (statement.trim() !== ';') {
        connection.query(statement, (err, results) => {
          if (err) {
            console.error('执行SQL失败:', err);
          } else {
            console.log(`表语句 ${index + 1} 执行成功`);
          }
          index++;
          executeNext();
        });
      } else {
        index++;
        executeNext();
      }
    }
    
    executeNext();
  });
});